package com.pptree.dao.impl;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.ArrayList;
import com.pptree.dao.NewsDAO;
import com.pptree.util.BaseDAO;
import com.pptree.vo.News;
import com.pptree.vo.NewsDomain;
import com.pptree.vo.PageVo;

public class NewsDAOImpl extends BaseDAO implements NewsDAO {

	@Override
	public List<News> findNewsTopNum(int uid, int num) {

		String sql = "select * from news where tid=? order by createdate desc limit ?";

		return findBySql(sql, new Object[] { uid, num });
	}

	@Override
	public int addNews(News news) {
		String sql = "insert into news (`tid`,`title`,`author`,`picPath`,`content`,`summary`) "
				+ "values (?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(news.getTid());
		values.add(news.getTitle());
		values.add(news.getAuthor());
		values.add(news.getPicpath());
		values.add(news.getContent());
		values.add(news.getSummary());
		return this.executeUpdate(sql, values.toArray());
	}

	@Override
	public int delNews(int nid) {
		String sql = "delete from news where id = ?";
		return this.executeUpdate(sql, nid);
	}

	@Override
	public int updateNews(News news) {
		String sql = "insert into news (`id`,`tid`,`title`,`author`,`picPath`,`content`,`summary`,`modifyDate`) "
				+ "values (?,?,?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(news.getId()); //!!
		values.add(news.getTid());
		values.add(news.getTitle());
		values.add(news.getAuthor());
		values.add(news.getPicpath());
		values.add(news.getContent());
		values.add(news.getSummary());
		values.add(news.getModifydate()); //!!
		return this.executeUpdate(sql, values.toArray());
	}

	private List<News> findBySql(String sql, Object... values) {
		List<News> newsList = new ArrayList<News>();
		rs = this.executeQuery(sql, values);
		try {
			while (rs.next()) {
				int id = rs.getInt("id");
				int tid = rs.getInt("tid");
				String title = rs.getString("title");
				String summary = rs.getString("summary");
				String content = rs.getString("content");
				String author = rs.getString("author");
				Timestamp createdate = rs.getTimestamp("createdate");
				String picpath = rs.getString("picpath");
				Timestamp modifydate = rs.getTimestamp("modifydate");
				News news = new News(id, tid, title, summary, content, author, createdate, picpath, modifydate);
				newsList.add(news);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, conn);
		}
		return newsList;
	}

	@Override
	public List<NewsDomain> findAllNews() {
		
		List<NewsDomain> newsDomainList = new ArrayList<NewsDomain>();
		String sql = "select n.id,c.name as tname,n.title,n.author,n.createdate,n.modifydate from news as n inner join category as c on (n.`tid`=c.`id`)";
		rs = this.executeQuery(sql, new Object[]{});
		try {
			
			while (rs.next()) {
				int id = rs.getInt("id");
				String tname = rs.getString("tname");
				String title = rs.getString("title");
				String author = rs.getString("author");
				Timestamp createdate = rs.getTimestamp("createdate");
				Timestamp modifydate = rs.getTimestamp("modifydate");
				
				NewsDomain newsDomain = new NewsDomain(id, tname, title, author, createdate, modifydate);
				
				newsDomainList.add(newsDomain);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, conn);
		}
		
		return newsDomainList;
	}

	@Override
	public News findById(int nid) {
		News news = null;
		String sql = "select * from news where id=?";
		rs = this.executeQuery(sql, nid);
		try {
			while (rs.next()) {
				news = new News();
				news.setId(rs.getInt("id"));
				news.setTid(rs.getInt("tid"));
				news.setTitle(rs.getString("title"));
				news.setAuthor(rs.getString("author"));
				news.setCreatedate(rs.getTimestamp("createDate"));
				news.setPicpath(rs.getString("picPath"));
				news.setContent(rs.getString("content"));
				news.setModifydate(rs.getTimestamp("modifyDate"));
				news.setSummary(rs.getString("summary"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(rs, pstmt, conn);
		}
		return news;
	}

	@Override
	public List<News> findNewsTopN(int top) {
		String sql = "select * from news order by RAND(),createdate desc limit ?";

		return findBySql(sql, new Object[] { top });
	}
	
	//获得总记录数
	public int getTotalCount(){
		String sql = "select count(1) as cnt from news";
		int total = 0;
		rs = this.executeQuery(sql, new Object[]{});
		try {
			if(rs.next()){
				total = rs.getInt("cnt");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			this.closeAll(rs, pstmt, conn);
		}
		return total;
	}

	//分页
	@Override
	public PageVo<NewsDomain> findByPaging(
			int pageno,  //当前页页码
			int pagesize  //页大小：每页显示多少条记录
		) 
	{ 
		PageVo<NewsDomain> pageVo = new PageVo<NewsDomain>();
		pageVo.setPageno(pageno);
		pageVo.setPagesize(pagesize);
		
		//分页SQL
		// sql = "select * from news order by createdate LIMIT ?,?";//不符合网站后的要求
		
		String sql = "select n.id,c.name as tname,n.title,n.author,n.createdate,n.modifydate from news as n inner join category as c on (n.`tid`=c.`id`) order by createdate desc LIMIT ?,?";
		int startRow = pageVo.getOffset();
		List<NewsDomain> newsList = new ArrayList<NewsDomain>();
		rs = this.executeQuery(sql, new Object[]{startRow,pagesize});
		try {
			while(rs.next()){
				int id = rs.getInt("id");
				String tname = rs.getString("tname");
				String title = rs.getString("title");
				String author = rs.getString("author");
				Timestamp createdate = rs.getTimestamp("createdate");
				Timestamp modifydate = rs.getTimestamp("modifydate");
				
				NewsDomain newsDomain = new NewsDomain(id, tname, title, author, createdate, modifydate);
				
				newsList.add(newsDomain);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			this.closeAll(rs, pstmt, conn);
		}
		
		pageVo.setList(newsList);
		int totalCount = getTotalCount();
		pageVo.setTotalcount(totalCount);
		return pageVo;
	}

}
